const prettyBytes = require("pretty-file-bytes");
const router = require("koa-router")();
const moment = require("moment");
const _ = require("lodash");
/**
 * api {get} /home/app/data/table 获取数据库所有表
 *
 *
 */
router.get("/table", async (ctx, nex) => {
  const baas = ctx.baas;
  const sql = `show table status from ${"`" + baas.database + "`"}`;

  const table = (await ctx.bookshelf.knex.raw(sql))[0];
  for (const key in table) {
    const dataLength = table[key].Data_length;
    table[key].Data_length = prettyBytes(dataLength);
  }

  ctx.success(table, "数据库所有表");
});
/**
 * api {get} /home/app/data/info/table 引擎列表.排序规则
 *
 *
 */
router.get("/info/table", async (ctx, nex) => {
  const user = ctx.user;
  const baas = ctx.baas;

  const engines = await ctx.engines(); // 获取引擎列表
  const collates = await ctx.collates(); // 获取排序规则

  const data = {
    engines: engines,
    collates: collates
  };

  ctx.success(data, "引擎列表.排序规则");
});

/**
 * api {post} /home/app/data/add/table 创建数据表
 *
 * apiParam {String} table 数据表名
 * apiParam {String} engine 引擎
 * apiParam {String} collate 校对
 *
 */
router.post("/add/table", async (ctx, nex) => {
  const baas = ctx.baas;
  const { table, engine, collate } = ctx.post;

  if (_.isEmpty(_.trim(table))) {
    ctx.error("", "数据表名不能为空");
    return;
  }

  let charset = "";
  const collates = await ctx.collates(); // 获取排序规则
  for (const row of collates) {
    const indexof = _.indexOf(row.sub, collate);
    if (indexof >= 0) {
      charset = row.name;
    }
  }
  try {
    const result = await ctx.bookshelf.knex.schema
      .withSchema(baas.database)
      .createTable(table, table => {
        table.increments();
        if (engine) {
          table.engine(engine);
        }
        if (collate) {
          table.collate(collate);
        }
        if (charset) {
          table.charset(charset);
        }
        table.timestamp("created_at").nullable();
        table.timestamp("updated_at").nullable();
        table.timestamp("deleted_at").nullable();
      });
    ctx.success(result[0], "创建成功");
  } catch (err) {
    ctx.error(err, err.sqlMessage);
  }
});

/**
 * api {get} /home/app/data/del/table/:tableName 删除数据表
 *
 *
 */
router.get("/del/table/:tableName", async (ctx, nex) => {
  const baas = ctx.baas;
  const { tableName } = ctx.params;

  try {
    const result = await ctx.bookshelf.knex.schema
      .withSchema(baas.database)
      .dropTableIfExists(tableName);
    ctx.success(result[0], "删除成功");
  } catch (err) {
    ctx.error(err, err.sqlMessage);
  }
});
/**
 * api {get} /home/app/data/truncate/table/:tableName 清空数据表
 *
 *
 */
router.get("/truncate/table/:tableName", async (ctx, nex) => {
  const baas = ctx.baas;
  const { tableName } = ctx.params;

  try {
    const result = await ctx.bookshelf
      .knex(baas.database + "." + tableName)
      .truncate();
    ctx.success(result[0], "清空成功");
  } catch (err) {
    ctx.error(err, err.sqlMessage);
  }
});
/**
 * api {get} /home/app/data/select/table/:tableName 搜索条件
 *
 *
 */
router.get("/select/table/:tableName", async (ctx, nex) => {
  const baas = ctx.baas;
  const { tableName } = ctx.params;

  // 函数
  const functions = ctx.functions();
  // 集合
  const grouping = ctx.grouping();
  // 搜索
  const operators = ctx.operators();
  // 字段
  const fields = await ctx.fields(baas.database, tableName);

  const data = {
    functions: functions,
    grouping: grouping,
    operators: operators,
    fields: fields
  };

  ctx.success(data, "搜索条件");
});

/**
 * api {post} /home/app/data/select/table/:tableName 选择数据
 * 
 	{
		select: [{
			func: 'min',
			column: 'id'
		}],
		where: [{
			column: 'id',
			func: '=',
			value: '1'
		}],
		order: [{
			column: 'id',
			desc: 1
		}],
		limit: 50,
		page: 1,
	}
 * 
 */
router.post("/select/table/:tableName", async (ctx, nex) => {
  const baas = ctx.baas;
  const { tableName } = ctx.params;
  const {
    select = [],
    where = [],
    order = [],
    limit = 50,
    page = 1
  } = ctx.post;

  // 选择
  const selectOr = [];
  for (const row of select) {
    if (row.func && row.column) {
      selectOr.push("{" + row.func + "(`" + row.column + "`)" + "}");
    }
  }

  const offset = page ? limit * (page - 1) : 0;
  const fields = await ctx.fields(baas.database, tableName);

  let whereOr = "";
  for (const row of where) {
    if (row.column) {
      whereOr += whereOr
        ? " and `" + row.column + "` " + row.func + " '" + row.value + "'"
        : "`" + row.column + "` " + row.func + " '" + row.value + "'";
    } else {
      if (row.value) {
        let orwhere = "";
        if (whereOr) {
          whereOr += " and (";
        } else {
          whereOr += " (";
        }
        for (const field of fields) {
          orwhere += orwhere
            ? " or `" + field.Field + "` " + row.func + " '" + row.value + "'"
            : "`" + field.Field + "` " + row.func + " '" + row.value + "'";
        }
        whereOr += orwhere + ")";
      }
    }
  }
  let orderBy = "";
  for (const row of order) {
    if (row.column) {
      orderBy += "`" + row.column + "` " + (row.desc ? "desc" : "") + ",";
    }
  }
  if (orderBy) {
    orderBy = orderBy.substr(0, orderBy.length - 1);
  } else {
    orderBy = "id asc";
  }

  let dataSql = await ctx.bookshelf
    .knex(baas.database + "." + tableName)
    .select(selectOr)
    .whereRaw(whereOr)
    .orderByRaw(orderBy)
    .limit(limit)
    .offset(offset)
    .toString();
  dataSql = dataSql.replace(/`{|}`/g, "");
  dataSql = dataSql.replace(/``/g, "`");

  const result = (await ctx.bookshelf.knex.raw(dataSql))[0];
  for (const k in result) {
    if (ctx.isSet(result[k].created_at)) {
      result[k].created_at = moment(result[k].created_at).format(
        "YYYY-MM-DD HH:mm:ss"
      );
    }
    if (ctx.isSet(result[k].updated_at)) {
      result[k].updated_at = moment(result[k].updated_at).format(
        "YYYY-MM-DD HH:mm:ss"
      );
    }
    if (ctx.isSet(result[k].deleted_at)) {
      result[k].deleted_at = moment(result[k].deleted_at).format(
        "YYYY-MM-DD HH:mm:ss"
      );
    }
  }

  let countSql = await ctx.bookshelf
    .knex(baas.database + "." + tableName)
    .select(selectOr)
    .whereRaw(whereOr)
    .count()
    .toString();
  countSql = countSql.replace(/`{|}`/g, "");
  countSql = countSql.replace(/``/g, "`");

  const countData = (await ctx.bookshelf.knex.raw(countSql))[0];
  const count = countData.length ? countData[0]["count(*)"] : 0;

  let pageCount = 1;
  if (limit) {
    pageCount = Math.ceil(count / limit);
  }
  const data = {
    fields: _.keys(result[0]),
    data: result,
    pagination: {
      page: page,
      pageCount: pageCount,
      pageSize: limit,
      rowCount: count
    }
  };

  ctx.success(data, "数据查询");
});

/**
 * api {get} /home/app/data/structure/table/:tableName 数据结构
 *
 *
 */
router.get("/structure/table/:tableName", async (ctx, nex) => {
  const baas = ctx.baas;
  const { tableName } = ctx.params;

  const fields = await ctx.fields(baas.database, tableName);

  const indexesSql =
    "show index from `" + baas.database + "`.`" + tableName + "`";
  const indexes = await ctx.bookshelf.knex.raw(indexesSql);

  const data = {
    fields: fields,
    indexes: indexes[0]
  };
  ctx.success(data, "表结构+索引");
});
/**
 * api {post} /home/app/data/table/:tableName/add/indexes/:keyName 新增索引
 *
 *
 */
router.get("/table/:tableName/add/indexes/:keyName", async (ctx, nex) => {
  const baas = ctx.baas;
  const { tableName, keyName } = ctx.params;

  let sql = await ctx.bookshelf.knex.schema
    .withSchema(baas.database)
    .alterTable(tableName, table => {
      table.index(keyName, []);
    })
    .toString();
  sql = sql.replace(/``/g, "");

  try {
    const result = await ctx.bookshelf.knex.raw(sql);
    ctx.success(result[0], "操作成功");
  } catch (err) {
    ctx.error(err, err.sqlMessage);
  }
});
/**
 * api {get} /home/app/data/indexes/table/:tableName 索引列表
 *
 */
router.get("/indexes/table/:tableName", async (ctx, nex) => {
  const baas = ctx.baas;
  const { tableName } = ctx.params;
  const indexesSql =
    "show index from `" + baas.database + "`.`" + tableName + "`";
  let indexes = (await ctx.bookshelf.knex.raw(indexesSql))[0];
  for (const row of indexes) {
    const name = row.Key_name;
    row.type =
      name == "PRIMARY"
        ? "PRIMARY"
        : row.Index_type == "FULLTEXT"
          ? "FULLTEXT"
          : row.Non_unique
            ? row.Index_type == "SPATIAL" ? "SPATIAL" : "INDEX"
            : "UNIQUE";
    row.lengths = row.Index_type == "SPATIAL" ? null : row.Sub_part;
  }
  indexes = _.groupBy(indexes, "Key_name");
  const data = [];
  for (const key in indexes) {
    data.push({
      type: indexes[key][0].type,
      name: indexes[key][0].Key_name,
      sub: indexes[key]
    });
  }

  ctx.success(data, "索引列表");
});
/**
 * api {post} /home/app/data/table/:tableName/add/indexes 修改索引
 * 
 * apiParam {String}
 {
    type: "PRIMARY",
    name: "status",
    sub: [
      {
        name: "id",
        length: ""
      },
      {
        name: "id",
        length: "11"
      }
    ]
  }
 * 
 */
router.post("/table/:tableName/add/indexes", async (ctx, nex) => {
  const baas = ctx.baas;
  const { tableName } = ctx.params;
  const data = ctx.post;

  const indexes = [];
  for (const row of data.sub) {
    if (row.name) {
      indexes.push(row.name + (row.length ? "`(" + row.length + ")`" : ""));
    }
  }

  let sql = await ctx.bookshelf.knex.schema
    .withSchema(baas.database)
    .alterTable(tableName, table => {
      if (data.type == "PRIMARY") {
        table.dropPrimary();
        table.primary(indexes, data.name);
      }
      if (data.type == "INDEX") {
        table.dropIndex("", data.name);
        table.index(indexes, data.name);
      }
      if (data.type == "UNIQUE") {
        table.dropIndex("", data.name);
        table.unique(indexes, data.name);
      }
    })
    .toString();
  sql = sql.replace(/``\(/g, "`(");
  sql = sql.replace(/```/g, "");
  sql = sql.replace(";", ",");
  sql = sql.replace(
    "alter table `" + baas.database + "`.`" + tableName + "` add",
    " add"
  );
  console.log(sql);
  try {
    const result = await ctx.bookshelf.knex.raw(sql);
    ctx.success(result[0], "操作成功");
  } catch (err) {
    ctx.error(err, err.sqlMessage);
  }
});
/**
 * api {post} /home/app/data/table/:tableName/edit/indexes 修改索引
 * 
 * apiParam {String}
 {
 		type : 'edit',
		key_name: 'PRIMARY',
		sub: [{
			column_name: 'id',
			column_length: '11'
		},{
			column_name: 'name',
			column_length: '11'
		}]
	}
 * 
 */
router.post("/table/:tableName/edit/indexes", async (ctx, nex) => {
  const baas = ctx.baas;
  const { tableName } = ctx.params;

  const keyName = ctx.post.key_name ? ctx.post.key_name : "INDEX";
  const { type = "edit", sub = [] } = ctx.post;

  const indexes = [];
  for (const row of sub) {
    if (row.column_name) {
      indexes.push(
        row.column_name +
          (row.column_length ? "{" + row.column_length + "}" : "")
      );
    }
  }

  let sql = await ctx.bookshelf.knex.schema
    .withSchema(baas.database)
    .alterTable(tableName, table => {
      if (keyName == "PRIMARY") {
        if (type == "edit") {
          table.dropPrimary();
        }
        table.primary(indexes, "PRIMARY");
      }
      if (keyName == "INDEX") {
        if (type == "edit") {
          table.dropIndex("", "INDEX");
        }
        table.index(indexes, "INDEX");
      }
    })
    .toString();
  sql = sql.replace(/{/g, "`(");
  sql = sql.replace(/}`/g, ")");
  if (type == "edit") {
    sql = sql + " using btree";
  }
  try {
    const result = await ctx.bookshelf.knex.raw(sql);
    ctx.success(result[0], "操作成功");
  } catch (err) {
    ctx.error(err, err.sqlMessage);
  }
});
/**
 * api {post} /home/app/data/table/:tableName/del/indexes/:keyName 删除索引
 *
 *
 */
router.get("/table/:tableName/del/indexes/:keyName", async (ctx, nex) => {
  const baas = ctx.baas;
  const { tableName, keyName } = ctx.params;

  const sql = await ctx.bookshelf.knex.schema
    .withSchema(baas.database)
    .alterTable(tableName, table => {
      table.dropIndex("", keyName);
    })
    .toString();

  try {
    const result = await ctx.bookshelf.knex.raw(sql);
    ctx.success(result[0], "操作成功");
  } catch (err) {
    ctx.error(err, err.sqlMessage);
  }
});
/**
 * api {get} /home/app/data/table/:tableName/column 字段条件
 *
 */
router.get("/table/:tableName/info/column", async (ctx, nex) => {
  const baas = ctx.baas;
  const { tableName } = ctx.params;

  const fields = await ctx.fields(baas.database, tableName);

  const collates = await ctx.collates(); // 获取排序规则
  const types = await ctx.types(); // 字段类型列表

  const data = {
    fields: fields,
    collates: collates,
    types: types
  };

  ctx.success(data, "字段条件");
});
/**
 * api {get} /home/app/data/table/:tableName/column/:columnName 字段信息
 *
 */
router.get("/table/:tableName/column/:columnName", async (ctx, nex) => {
  const baas = ctx.baas;
  const { tableName, columnName } = ctx.params;

  const field = await ctx.fields(baas.database, tableName, columnName);

  ctx.success(field, "字段信息");
});
/**
 * api {post} /home/app/data/table/:tableName/add/column 新增修改字段
 * 
 * apiParam {String}
 	{
		old_field: 'name',
		new_field: 'name1',
		type: 'text',
		length: '11',
		null: 1,
		collate: 'utf8_general_ci',
		default: '',
		comment: '注释',
		after: ''
 	}
 * 
 */
router.post("/table/:tableName/add/column", async (ctx, nex) => {
  const baas = ctx.baas;
  const { tableName } = ctx.params;
  const data = ctx.post;
  if (_.isEmpty(_.trim(data.new_field))) {
    ctx.error("", "字段名不能为空");
    return;
  }

  const fieldOf = _.indexOf(
    ["id", "created_at", "updated_at", "deleted_at"],
    data.new_field
  );
  if (fieldOf >= 0) {
    ctx.error("", "默认字段不允许操作");
    return;
  }

  let sql = await ctx.bookshelf.knex.schema
    .withSchema(baas.database)
    .table(tableName, table => {
      const indexof = _.indexOf(
        [
          "tinyint",
          "smallint",
          "mediumint",
          "int",
          "bigint",
          "decimal",
          "float",
          "double",
          "real",
          "bit",
          "datetime",
          "timestamp",
          "time",
          "year",
          "char",
          "varchar",
          "enum",
          "set",
          "bit",
          "binary",
          "varbinary",
          "geometry",
          "point",
          "linestring",
          "polygon",
          "multipoint",
          "multilinestring",
          "multipolygon",
          "geometrycollection"
        ],
        data.type
      );

      let type = data.type;
      if (indexof >= 0 && ctx.isSet(data.length)) {
        type += "(" + data.length + ")";
      }

      const row = table.specificType(data.new_field, type);

      if (data.null) {
        row.nullable();
      } else {
        row.notNullable();
      }
      if (data.default) {
        row.defaultTo(data.default);
      }
      if (data.comment) {
        row.comment(data.comment);
      }
      if (data.collate) {
        row.collate(data.collate);
      }
    })
    .toString();
  if (data.old_field) {
    sql = sql.replace("add", "change " + "`" + data.old_field + "`");
  }
  if (data.after) {
    if (data.after == "first") {
      sql = sql + " first";
    } else {
      sql = sql + " after " + "`" + data.after + "`";
    }
  }

  try {
    const result = await ctx.bookshelf.knex.raw(sql);

    // 删除redis缓存
    await BaaS.redis.delAll(
      `baas:*:appid:${baas.appid}:appkey:${baas.appkey}:*:table:${tableName}:*`
    );

    ctx.success(result[0], "操作成功");
  } catch (err) {
    ctx.error(err, err.sqlMessage);
  }
});
/**
 * api {post} /home/app/data/table/:tableName/del/column/:columnName 删除字段
 *
 * apiParam {String} tableName 数据表名
 * apiParam {String} columnName 字段名
 *
 */
router.get("/table/:tableName/del/column/:columnName", async (ctx, nex) => {
  const baas = ctx.baas;
  const { tableName, columnName } = ctx.params;

  const fieldOf = _.indexOf(
    ["id", "created_at", "updated_at", "deleted_at"],
    columnName
  );
  if (fieldOf >= 0) {
    ctx.error("", "默认字段不允许删除");
    return;
  }

  try {
    const result = await ctx.bookshelf.knex.schema
      .withSchema(baas.database)
      .alterTable(tableName, table => {
        table.dropColumn(columnName);
      });

    // 删除redis缓存
    await BaaS.redis.delAll(
      `baas:*:appid:${baas.appid}:appkey:${baas.appkey}:*:table:${tableName}:*`
    );

    ctx.success(result[0], "操作成功");
  } catch (err) {
    ctx.error(err, "操作失败");
  }
});
/**
 * api {get} /home/app/data/table/:tableName/info/data 获取数据info
 *
 * apiParam {String} tableName 数据表名
 *
 */
router.get("/table/:tableName/info/data", async (ctx, nex) => {
  const baas = ctx.baas;
  const { tableName } = ctx.params;

  const functions = await ctx.fus();
  const fields = await ctx.fields(baas.database, tableName);

  const data = {
    functions: functions,
    data: []
  };
  for (const field of fields) {
    let columnInput = "string";
    const intof = _.indexOf(
      [
        "tinyint",
        "smallint",
        "mediumint",
        "int",
        "bigint",
        "decimal",
        "float",
        "double"
      ],
      field.Type_index
    );
    const strof = _.indexOf(
      ["char", "varchar", "tinytext", "text", "mediumtext", "longtext"],
      field.Type_index
    );
    const timeof = _.indexOf(
      ["date", "datetime", "timestamp", "time", "year"],
      field.Type_index
    );
    if (intof >= 0) {
      columnInput = "int";
    }
    if (strof >= 0) {
      columnInput = "string";
    }
    if (timeof >= 0) {
      columnInput = "time";
    }

    data.data.push({
      column_name: field.Field,
      column_type: field.Type_index,
      column_value: "",
      column_input: columnInput
    });
  }

  ctx.success(data, "数据info");
});
/**
 * api {get} /1.0/app/data/table/:tableName/data/:id 获取单个数据
 *
 * apiParam {String} tableName 数据表名
 * apiParam {String} id
 *
 */
router.get("/table/:tableName/data/:id", async (ctx, nex) => {
  const baas = ctx.baas;
  const { tableName, id } = ctx.params;

  const functions = await ctx.fus();
  const fields = await ctx.fields(baas.database, tableName);
  const result = (await ctx.bookshelf
    .knex(baas.database + "." + tableName)
    .where("id", id))[0];
  if (ctx.isSet(result.created_at)) {
    result.created_at = moment(result.created_at).format("YYYY-MM-DD HH:mm:ss");
  }
  if (ctx.isSet(result.updated_at)) {
    result.updated_at = moment(result.updated_at).format("YYYY-MM-DD HH:mm:ss");
  }
  if (ctx.isSet(result.deleted_at)) {
    result.deleted_at = moment(result.deleted_at).format("YYYY-MM-DD HH:mm:ss");
  }

  const data = {
    functions: functions,
    data: []
  };
  for (const field of fields) {
    let columnInput = "string";
    const intof = _.indexOf(
      [
        "tinyint",
        "smallint",
        "mediumint",
        "int",
        "bigint",
        "decimal",
        "float",
        "double"
      ],
      field.Type_index
    );
    const strof = _.indexOf(
      ["char", "varchar", "tinytext", "text", "mediumtext", "longtext"],
      field.Type_index
    );
    const timeof = _.indexOf(
      ["date", "datetime", "timestamp", "time", "year"],
      field.Type_index
    );
    if (intof >= 0) {
      columnInput = "int";
    }
    if (strof >= 0) {
      columnInput = "string";
    }
    if (timeof >= 0) {
      columnInput = "time";
    }

    data.data.push({
      column_name: field.Field,
      column_type: field.Type_index,
      column_value: result[field.Field],
      column_input: columnInput
    });
  }

  ctx.success(data, "数据info");
});

/**
 * api {post} /home/app/data/table/:tableName/add/data 新增修改数据
 * 
 *  {
 		ids: '',
		data:[{
			column_name: 'name',
			column_function: 'now',
			column_value: ''
		}]
	}
 * 
 */
router.post("/table/:tableName/add/data", async (ctx, nex) => {
  const baas = ctx.baas;
  const { tableName } = ctx.params;
  const data = ctx.post;

  // 组装数据
  const columnData = {};
  for (const row of data.data) {
    if (row.column_name != "id") {
      if (row.column_function) {
        let columnValue = row.column_value;
        columnValue = columnValue
          ? "'" + columnValue.replace(/,/g, "','") + "'"
          : "";

        columnData[row.column_name] =
          "{!}" + row.column_function + "(" + columnValue + ")" + "{!}";
      } else {
        if (row.column_value) {
          columnData[row.column_name] = row.column_value;
        }
      }
    }
  }

  let sql = "";
  if (data.ids) {
    sql = await ctx.bookshelf
      .knex(baas.database + "." + tableName)
      .whereIn("id", data.ids.split(","))
      .update(columnData)
      .toString();
  } else {
    columnData.created_at = "{!}now(){!}";
    sql = await ctx.bookshelf
      .knex(baas.database + "." + tableName)
      .insert(columnData)
      .toString();
  }
  console.log(sql);
  sql = sql.replace(/'{!}|{!}'/g, "");
  // sql = sql.replace(/\\/g, "");
  console.log(sql);

  try {
    const result = await ctx.bookshelf.knex.raw(sql);

    // 删除redis缓存
    await BaaS.redis.delAll(
      `baas:*:appid:${baas.appid}:appkey:${baas.appkey}:*:table:${tableName}:*`
    );

    ctx.success(result[0], "操作成功");
  } catch (err) {
    ctx.error(err, err.sqlMessage);
  }
});

/**
 * api {get} /home/app/data/table/:tableName/del/data/:ids 删除数据
 *
 * apiParam {Number} ids 数据id
 *
 */
router.get("/table/:tableName/del/data/:ids", async (ctx, nex) => {
  const baas = ctx.baas;
  const { tableName, ids } = ctx.params;

  try {
    const result = await ctx.bookshelf
      .knex(baas.database + "." + tableName)
      .whereIn("id", ids.split(","))
      .del();
    // 删除redis缓存
    await BaaS.redis.delAll(
      `baas:*:appid:${baas.appid}:appkey:${baas.appkey}:*:table:${tableName}:*`
    );

    ctx.success(result[0], "操作成功");
  } catch (err) {
    ctx.error(err, "操作失败");
  }
});

module.exports = router;
